/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Answer;
import dto.LoggedUser;
import dto.Question;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import util.Constants;
import util.DBConnection;

/**
 *
 * @author Shuvo
 */
public class FetchQuestions {
    
    public static List<Question> viewQuestions(String status, String subject, String userId, String userType) {
        Connection connection = null;
        Statement stmt = null;
        String query = "";
        if (Constants.Open.equalsIgnoreCase(status)) {
            if (Constants.Student.equalsIgnoreCase(userType)) {
                //query = "select * from question where subject_fk='" + subject + "' and ASKED_BY=" + userId + " and STATUS='" + status + "' and rownum>=" + offset + " and rownum<=" + noOfRecords;
                query = "select * from question where subject_fk='" + subject + "' and ASKED_BY=" + userId + " and STATUS='Open'";
            } else {
                query = "select * from question where subject_fk='" + subject + "' and STATUS='Open'";
            }

        } else if (Constants.Rejected.equalsIgnoreCase(status)) {
            if (Constants.Student.equalsIgnoreCase(userType)) {
                query = "select * from question where subject_fk='" + subject + "' and ASKED_BY=" + userId + " and STATUS='Rejected'";
            } else if (Constants.Faculty.equalsIgnoreCase(userType)) {
                query = "select * from question where subject_fk='" + subject + "' and REVIEWED_BY=" + userId + " and STATUS='Rejected'";
            } else {
                query = "select * from question where subject_fk='" + subject + "' and STATUS='Rejected'";
            }
        } else {
            query = "select * from question where subject_fk='" + subject + "' and STATUS='Approved'";
        }
        List<Question> list = new ArrayList<Question>();
        Question question = null;
        try {
            connection = DBConnection.getConn();
            stmt = connection.createStatement();
            query = query + " order by ques_id";
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                question = new Question();
                String quesId = rs.getString(1);
                question.setQuesId(quesId);
                question.setText(rs.getString(2));
                question.setSubject(rs.getString(3));
                question.setAskedBy(rs.getString(4));
                question.setTimestamp(rs.getString(5));
                question.setStatus(status);
                question.setReviewedBy(rs.getString(7));
                list.add(question);
            }
            rs.close();

            if (!list.isEmpty()) {
                for (int i = 0; i < list.size(); i++) {
                    Question q = list.get(i);
                    ResultSet replies = stmt.executeQuery("select count(*) from answer where QUES_FK=" + q.getQuesId());
                    if (replies.next()) {
                        q.setReplies(replies.getInt(1));
                    }
                    replies = stmt.executeQuery("select FULL_NAME from LOGIN_DETAILS where USER_ID=" + q.getAskedBy());
                    if (replies.next()) {
                        q.setAskedBy(replies.getString(1));
                    }
                    replies.close();
                }
            }

            //this.noOfRecords = list.size();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public static void viewQuestion(String subject, String quesId, LoggedUser loggedUser, Question question, List<Answer> ansList) {
        Connection connection = null;
        Statement stmt = null;
        String query = "select * from question where QUES_ID=" + quesId + " and SUBJECT_FK='"+subject+"'";
        if(!loggedUser.getSubjects().contains(subject)) {
            return;
        }
        
        try {
            connection = DBConnection.getConn();
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                String status = rs.getString(6);
                String askedBy = rs.getString(4);
                String reviewedBy = rs.getString(7);
                if(Constants.Open.equalsIgnoreCase(status) || Constants.Rejected.equalsIgnoreCase(status)) {
                    if(Constants.Student.equalsIgnoreCase(loggedUser.getType()) && !askedBy.equalsIgnoreCase(loggedUser.getUserId())) {
                        question.setQuesId("-1");
                        return;
                    } else if (Constants.Faculty.equalsIgnoreCase(loggedUser.getType()) && reviewedBy != null &&
                            !reviewedBy.equalsIgnoreCase(loggedUser.getUserId())) {
                        question.setQuesId("-1");
                        return;
                    }
                }
                question.setQuesId(quesId);
                question.setText(rs.getString(2));
                question.setSubject(subject);
                question.setAskedBy(rs.getString(4));
                question.setTimestamp(rs.getString(5));
                question.setStatus(status);
                question.setReviewedBy(rs.getString(7));

                rs = stmt.executeQuery("select count(*) from answer where QUES_FK=" + quesId);
                if (rs.next()) {
                    question.setReplies(rs.getInt(1));
                }
                rs = stmt.executeQuery("select FULL_NAME from LOGIN_DETAILS where USER_ID=" + question.getAskedBy());
                if (rs.next()) {
                    question.setAskedBy(rs.getString(1));
                }
                rs = stmt.executeQuery("select FULL_NAME from LOGIN_DETAILS where USER_ID=" + question.getReviewedBy());
                if (rs.next()) {
                    question.setReviewedBy(rs.getString(1));
                }
                rs = stmt.executeQuery("select * from answer where QUES_FK=" + quesId +" order by to_timestamp(ANS_TIME)");
                while(rs.next()) {
                    Answer ans = new Answer();
                    ans.setQuedId(quesId);
                    ans.setAnsText(rs.getString(2));
                    ans.setAnsBy(rs.getString(3));
                    ans.setTimeStamp(rs.getString(4));
                    ansList.add(ans);
                }

                if (!ansList.isEmpty()) {
                    for (int i = 0; i < ansList.size(); i++) {
                        Answer ans = ansList.get(i);
                        rs = stmt.executeQuery("select FULL_NAME from LOGIN_DETAILS where USER_ID=" + ans.getAnsBy());
                        if (rs.next()) {
                            ans.setAnsBy(rs.getString(1));
                        }
                    }
                }
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
